from osv import fields,osv
import mx.DateTime
import tools.sql
from tools.translate import _

class kdvn_summary_receivable_items(osv.osv):
    _name='kdvn.summary.receivable.items'
    _description='KDVN Report Summary Receivable Items'
    _auto=False
    _columns={
              'number':fields.char('Payment No.',size=15,select=1),
              'date':fields.date('Date'),
              'prj_e_id':fields.many2one('kdvn.project','Project E.',select=1),
              'prj_m_id':fields.many2one('kdvn.project','Project M.',select=1),
              'contract_id':fields.many2one('kdvn.contract.client','Contract',select=1),
              'currency_id':fields.many2one('project.currency','Cur.'),
              'cost_progress':fields.float('Progress Cost'),
              'vat':fields.float('V.A.T'),
              'contract_total':fields.float('Contract Total'),
              'description':fields.char('Description',size=255),
              'client_id':fields.many2one('res.partner','Client'),
              'project_type':fields.char('Project Type',size=4),
              'returned_contract':fields.boolean('Contract Returned?'),
              'returned_progress':fields.boolean('Progress Returned?'),
              'red_invoice_ok':fields.boolean('V.A.T Invoice?'),
              'remarks':fields.char('Remarks',size=255),
              'year':fields.integer('Year',select=1)
              }
    def init1(self,cr):
        tools.sql.drop_view_if_exists(cr,'kdvn_summary_receivable_items')
        cr.execute("CREATE OR REPLACE VIEW kdvn_summary_receivable_items as\
                        Select \
                            kpfc.id,\
                            number,\
                            kpfc.date,\
                            kpfc.contract_id,\
                            prj_e_id,\
                            prj_m_id,\
                            contract_currency as currency_id,\
                            coalesce(adv_payment,0)+coalesce(retention,0)+coalesce(others,0)+coalesce(amount_e,0)+coalesce(amount_m,0) as cost_progress,\
                            coalesce(kdvn_amount_tax,0) as vat,\
                            coalesce(contract_total,0) as contract_total,\
                            itemofrequest as description,\
                            kpfc.client_id,\
                            case when \
                                coalesce(prj_e_id,0)>0 and\
                                coalesce(prj_m_id,0)>0 \
                            then\
                                'E/M'\
                            else\
                                case when coalesce(prj_e_id,0)>0 then\
                                    'E'\
                                else\
                                    case when coalesce(prj_m_id,0)>0 then\
                                        'M'\
                                    else\
                                        'X'\
                                    end\
                                end\
                            end as project_type,\
                            coalesce(attached_contract_received,False) as returned_contract,\
                            coalesce(kpfc.attached_progress_received,False) as returned_progress,\
                            kpfc.description as remarks,\
                            case when coalesce(red_invoice_out.id,0)=0 then false else true end as red_invoice_ok,\
                            extract(year from kpfc.date) as year\
                        from \
                            kdvn_payment_from_client kpfc\
                        left join\
                            kdvn_contract_client kcc on contract_id=kcc.id\
                        left join\
                            (Select \
                                kpcr.contract_id as id,\
                                max(kpe.id) as prj_e_id,\
                                max(kpm.id) as prj_m_id\
                            from\
                                kdvn_project_contract_rel kpcr\
                            left join\
                                kdvn_project kpe on project_id=kpe.id and kpe.project_type='E'\
                            left join\
                                kdvn_project kpm on project_id=kpm.id and kpm.project_type='M'\
                            group by\
                                kpcr.contract_id) vwcotract_project on kpfc.contract_id=vwcotract_project.id\
                        left join\
                            (Select distinct\
                                payment_id as id\
                            from \
                                payment_from_client_kdvn_red_invoice_rel\
                            inner join\
                                kdvn_red_invoice kri on red_invoice_id = kri.id) red_invoice_out on kpfc.id = red_invoice_out.id\
                        Where \
                            state not in ('draft','cancel') and \
                            kpfc.id not in\
                                (select \
                                        payment_id\
                                from \
                                    kdvn_received\
                                where \
                                    actual_amount>0 and\
                                    payment_id in \
                                        (select id from kdvn_payment_from_client where state not in ('draft','cancel')));")
kdvn_summary_receivable_items()

class kdvn_payment_from_client(osv.osv):
    _name = 'kdvn.payment.from.client'
    _inherit = 'kdvn.payment.from.client'
    _description="KDVN Payment From Client"
    
    _columns={
           #   'receivable_id':fields.one2many('kdvn.summary.receivable.items','id','Receivable')
              } 
kdvn_payment_from_client()